package com.ttg.web.service.Impl;

import com.google.common.base.CaseFormat;
import com.ttg.common.constant.BasicConstant;
import com.ttg.common.constant.ParentDateTypeConstant;
import com.ttg.common.dto.*;
import com.ttg.common.enums.*;
import com.ttg.common.utils.DateTimeUtil;
import com.ttg.model.dto.ConditionsInfoDTO;
import com.ttg.model.dto.TagInfoDTO;
import com.ttg.model.pojo.*;
import com.ttg.web.dao.*;
import com.ttg.web.service.EstimateTagInfoService;
import com.ttg.web.service.ManageTagInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class EstimateTagInfoServiceImpl implements EstimateTagInfoService {

    @Autowired
    private ConditionsNameDao conditionsNameDao;

    @Autowired
    private TagHisDao tagHisDao;

    @Autowired
    private ManageTagInfoService manageTagInfoService;

    @Override
    public Integer tagEstimateVip(TagInfoDTO reqDto) {
        List<ConditionsInfo> byTagId = new ArrayList<>();
        Long tagId = System.currentTimeMillis();
        for (ConditionsInfoDTO t : reqDto.getConditionsInfoDTOList()) {
            ConditionsInfo a = new ConditionsInfo();
            BeanUtils.copyProperties(t, a);
            a.setTagId(tagId);
            byTagId.add(a);
        }
        List<TagProducer> tagProducers = new ArrayList<>();
        for (int s = 0; s < byTagId.size(); s++) {
            ConditionsInfo conditionsInfo = byTagId.get(s);
            ConditionsName conditionsName = conditionsNameDao.getById(conditionsInfo.getConditionsId());
            Integer classId = conditionsName.getClassId();
            ConditionsClassEnum conditionsClassEnum = ConditionsClassEnum.getByValue(classId);
            if (Objects.isNull(conditionsClassEnum)) {
                break;
            }

            ParentDateTypeConstant parentDateTypeConstant = getParentDateTypeConstant(conditionsName.getConditionsDataTypeId());
            if (Objects.isNull(parentDateTypeConstant)) {
                break;
            }
            String columnName = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, conditionsName.getConditionsValueKey());
            Long conditionsExpressionId = conditionsInfo.getConditionsExpressionId();
            //匹配对应表达式
            if (Objects.nonNull(conditionsExpressionId)) {
                int i = conditionsExpressionId.intValue();
                if (i == ExpressionTypeEnum.TYPE_BETWEEN.getValue()) {
                    if (!parentDateTypeConstant.between()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    TagProducer tagProducer2 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr(">=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer2.setColumnName(columnName);
                    tagProducer2.setCompareStr("<=");
                    tagProducer2.setValue(conditionsInfo.getData2());
                    tagProducer2.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                    tagProducers.add(tagProducer2);
                } else if (i == ExpressionTypeEnum.TYPE_NOT_BETWEEN.getValue()) {
                    if (!parentDateTypeConstant.notBetween()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setOrFlag(BasicConstant.INT_1);
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    List<TagProducerForOR> orList = new ArrayList<>();
                    TagProducerForOR forOR1 = new TagProducerForOR();
                    TagProducerForOR forOR2 = new TagProducerForOR();
                    forOR1.setCompareStr("<=");
                    forOR1.setValue(conditionsInfo.getData1());
                    forOR2.setCompareStr(">=");
                    forOR2.setValue(conditionsInfo.getData2());
                    orList.add(forOR1);
                    orList.add(forOR2);
                    tagProducer1.setOrList(orList);
                    tagProducers.add(tagProducer1);
                } else if (i == ExpressionTypeEnum.TYPE_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.equalTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());

                    if (conditionsInfo.getData1().contains(";")) {
                        tagProducer1.setCompareStr(" IN ");
                        String data = conditionsInfo.getData1().replaceAll(";", "','");
                        tagProducer1.setValue("('" + data + "')");
                    } else {
                        tagProducer1.setCompareStr("=");
                        tagProducer1.setValue(conditionsInfo.getData1());
                    }
                    tagProducers.add(tagProducer1);

                } else if (i == ExpressionTypeEnum.TYPE_NOTEQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.notEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr("!=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                } else if (i == ExpressionTypeEnum.TYPE_GREATER_THAN.getValue()) {
                    if (!parentDateTypeConstant.greaterThan()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr(">");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                } else if (i == ExpressionTypeEnum.TYPE_LESS_THAN.getValue()) {
                    if (!parentDateTypeConstant.lessThan()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr("<");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                } else if (i == ExpressionTypeEnum.TYPE_GREATER_THAN_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.greaterThanEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr(">=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);

                } else if (i == ExpressionTypeEnum.TYPE_LESS_THAN_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.lessThanEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr("<=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                } else if (i == ExpressionTypeEnum.TYPE_LIKE.getValue()) {
                    if (!parentDateTypeConstant.like()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr(" LIKE ");
                    tagProducer1.setValue("%" + conditionsInfo.getData1() + "%");
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducers.add(tagProducer1);
                }
            }
        }
        return forPrivateTagProducer(tagProducers, tagId);
    }

    public Integer forPrivateTagProducer(List<TagProducer> tags, Long tagId) {
        //根据条件字段所在表分组
        Map<Integer, List<TagProducer>> tagsMap = tags.stream().collect(Collectors.groupingBy(TagProducer::getConditionsClassEnumValue));
        DecimalFormat decimalFormat = new DecimalFormat("0000");
        String format = "2" + decimalFormat.format(tagId);
        StringBuilder builder = new StringBuilder("SELECT CONCAT(" + format + ",vip_id)," + tagId + ",vip_id,'" + DateTimeUtil.formatYYYY_MM_DD_HH_MM_SS(LocalDateTime.now()) + "' FROM tbl_app_coupon_vip");
        //是否有主表查询条件
        List<TagProducer> tagProducerMain = tagsMap.get(ConditionsClassEnum.BANK_CUSTOMER_INFO.getValue());
        boolean tbMainFlag = !CollectionUtils.isEmpty(tagProducerMain);
        if (tbMainFlag) {
            //主表不参与for循环构造连表sql
            tagsMap.remove(ConditionsClassEnum.BANK_CUSTOMER_INFO.getValue());
        }
        if (!CollectionUtils.isEmpty(tagsMap)) {
            List<Integer> keys = new ArrayList<>(tagsMap.keySet());
            //连表sql构造
            for (int i = 0; i < keys.size(); i++) {
                Integer conditionsClassEnumValue = keys.get(i);
                //表名
                String tableName = ConditionsClassEnum.getTableNameByValue(conditionsClassEnumValue);
                List<TagProducer> tbProducers = tagsMap.get(conditionsClassEnumValue);
                builder.append(" INNER JOIN ( SELECT cst_id AS cst").append(i);
                StringBuilder having = new StringBuilder(" HAVING ");
                boolean havingFlag = false;
                if (ConditionsClassEnum.needSumByAgent(conditionsClassEnumValue)) {
                    List<TagProducer> removeProducers = new ArrayList<>();
                    List<String> sumColumnName = new ArrayList<>();
                    for (int j = 0; j < tbProducers.size(); j++) {
                        TagProducer producer = tbProducers.get(j);
                        if (sumColumnName.contains(producer.getColumnName())) {
                            removeProducers.add(producer);
                            having.append(" AND ").append(producer.getColumnName()).append(producer.getCompareStr()).append(producer.getValue());
                            continue;
                        }
                        if (producer.getColumnName().contains("amount")) {
                            builder.append(",SUM(").append(producer.getColumnName()).append(") AS ").append(producer.getColumnName());
                            if (j != 0) {
                                having.append(" AND");
                            }
                            having.append(" ").append(producer.getColumnName()).append(producer.getCompareStr()).append(producer.getValue());
                            removeProducers.add(producer);
                            sumColumnName.add(producer.getColumnName());
                            havingFlag = true;
                        }
                    }
                    tbProducers.removeAll(removeProducers);
                }
                String tbAlias = "t" + conditionsClassEnumValue;
                builder.append(" FROM ").append(tableName).append(" ").append(tbAlias);
                if (conditionsClassEnumValue.equals(ConditionsClassEnum.BANK_CARD_BINDING_INFO.getValue())) {
                    builder.append(" INNER JOIN tbl_bank_card_data_info b ON ").append(tbAlias).append(".bank_card_number = b.bank_card_number");
                }
                for (int j = 0; j < tbProducers.size(); j++) {
                    TagProducer tbProducer = tbProducers.get(j);
                    if (j != 0) {
                        builder.append(" AND ");
                    } else {
                        builder.append(" WHERE ");
                    }
                    if (tbProducer.getOrFlag() == 1) {
                        builder.append("(");
                        for (int k = 0; k < tbProducer.getOrList().size(); k++) {
                            TagProducerForOR forOR = tbProducer.getOrList().get(k);
                            if ((k != 0)) {
                                builder.append(" OR ");
                            }
                            builder.append(tbAlias).append(".").append(tbProducer.getColumnName()).append(forOR.getCompareStr()).append("'").append(forOR.getValue()).append("'");
                        }
                        builder.append(")");
                        continue;
                    }
                    if (tbProducer.getCompareStr().contains("IN")) {
                        builder.append(tbAlias).append(".").append(tbProducer.getColumnName()).append(tbProducer.getCompareStr()).append(tbProducer.getValue());
                    } else {
                        builder.append(tbAlias).append(".").append(tbProducer.getColumnName()).append(tbProducer.getCompareStr()).append("'").append(tbProducer.getValue()).append("'");
                    }
                }
                if (havingFlag) {
                    builder.append(" GROUP BY cst_id").append(having);
                }
                builder.append(") t").append(i).append(" ON cst").append(i).append(" = ").append("vip_cst_id");
            }
        }
        if (tbMainFlag) {
            builder.append(" WHERE ");
            for (int s = 0; s < tagProducerMain.size(); s++) {
                TagProducer mainTagProducer = tagProducerMain.get(s);
                if (s != 0) {
                    builder.append(" AND ");
                }
                if (mainTagProducer.getCompareStr().contains("IN")) {
                    builder.append(mainTagProducer.getColumnName()).append(mainTagProducer.getCompareStr()).append(mainTagProducer.getValue());
                } else {
                    builder.append(mainTagProducer.getColumnName()).append(mainTagProducer.getCompareStr()).append("'").append(mainTagProducer.getValue()).append("'");
                }
            }
        }
        builder.append(" GROUP BY vip_id");
        String sql = builder.toString();
        sql="select count(1) from ("+sql+") a";
        log.info("forPrivateTagProducer 拼接sql: " + sql);
        int tagVipNum = tagHisDao.getEstimate(sql);
        return tagVipNum;
    }

    public ParentDateTypeConstant getParentDateTypeConstant(Long dataTypeId) {
        if (ConditionsDataTypeEnum.TYPE_NUMBER.getValue() == dataTypeId) {
            return new NumberDataType();
        } else if (ConditionsDataTypeEnum.TYPE_STRING.getValue() == dataTypeId) {
            return new StringDataType();
        } else if (ConditionsDataTypeEnum.TYPE_BOOLEAN.getValue() == dataTypeId) {
            return new BooleanDataType();
        } else if (ConditionsDataTypeEnum.TYPE_DATE.getValue() == dataTypeId) {
            return new DateDataType();
        } else if (ConditionsDataTypeEnum.TYPE_STRING_VAGUE.getValue() == dataTypeId) {
            return new StringVagueDataType();
        }
        return null;
    }

    @Override
    public Integer tagEstimateMct(TagInfoDTO reqDto) {
        List<TagProducer> tagProducers = new ArrayList<>();
        List<TagProducer> tagPrivateProducers = new ArrayList<>();
        List<ConditionsInfo> byTagId = new ArrayList<>();
        Long tagId = System.currentTimeMillis();
        for (ConditionsInfoDTO t : reqDto.getConditionsInfoDTOList()) {
            ConditionsInfo a = new ConditionsInfo();
            BeanUtils.copyProperties(t, a);
            a.setTagId(tagId);
            byTagId.add(a);
        }
        for (int s = 0; s < byTagId.size(); s++) {
            ConditionsInfo conditionsInfo = byTagId.get(s);
            ConditionsName conditionsName = conditionsNameDao.getById(conditionsInfo.getConditionsId());
            Integer classId = conditionsName.getClassId();
            ConditionsClassEnum conditionsClassEnum = ConditionsClassEnum.getByValue(classId);
            if (Objects.isNull(conditionsClassEnum)) {
                break;
            }
            //是否是对私会员相关表
            Boolean joinPrivateFlag = ConditionsClassEnum.forPrivateEnums().contains(conditionsClassEnum.getValue());

            ParentDateTypeConstant parentDateTypeConstant = getParentDateTypeConstant(conditionsName.getConditionsDataTypeId());
            if (Objects.isNull(parentDateTypeConstant)) {
                break;
            }

            String columnName = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, conditionsName.getConditionsValueKey());
            Long conditionsExpressionId = conditionsInfo.getConditionsExpressionId();
            //匹配对应表达式
            if (Objects.nonNull(conditionsExpressionId)) {
                int i = conditionsExpressionId.intValue();
                if (i == ExpressionTypeEnum.TYPE_BETWEEN.getValue()) {
                    if (!parentDateTypeConstant.between()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    TagProducer tagProducer2 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr(">=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    tagProducer2.setColumnName(columnName);
                    tagProducer2.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer2.setCompareStr("<=");
                    tagProducer2.setValue(conditionsInfo.getData2());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                        tagPrivateProducers.add(tagProducer2);
                    } else {
                        tagProducers.add(tagProducer1);
                        tagProducers.add(tagProducer2);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_NOT_BETWEEN.getValue()) {
                    if (!parentDateTypeConstant.notBetween()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setOrFlag(BasicConstant.INT_1);
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    List<TagProducerForOR> orList = new ArrayList<>();
                    TagProducerForOR forOR1 = new TagProducerForOR();
                    TagProducerForOR forOR2 = new TagProducerForOR();
                    forOR1.setCompareStr("<=");
                    forOR1.setValue(conditionsInfo.getData1());
                    forOR2.setCompareStr(">=");
                    forOR2.setValue(conditionsInfo.getData2());
                    orList.add(forOR1);
                    orList.add(forOR2);
                    tagProducer1.setOrList(orList);
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.equalTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    if ("mct_industry".equals(columnName)) {
                        //有所属行业条件
                        tagProducer1.setConditionsClassEnumValue(ConditionsClassEnum.BANK_PUBLIC_INDUSTRY.getValue());
                    } else {
                        tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    }
                    if (conditionsInfo.getData1().contains(";")) {
                        tagProducer1.setCompareStr(" IN ");
                        String data = conditionsInfo.getData1().replaceAll(";", "','");
                        tagProducer1.setValue("('" + data + "')");
                    } else {
                        tagProducer1.setCompareStr("=");
                        tagProducer1.setValue(conditionsInfo.getData1());
                    }
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_NOTEQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.notEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr("!=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_GREATER_THAN.getValue()) {
                    if (!parentDateTypeConstant.greaterThan()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr(">");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_LESS_THAN.getValue()) {
                    if (!parentDateTypeConstant.lessThan()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr("<");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }

                } else if (i == ExpressionTypeEnum.TYPE_GREATER_THAN_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.greaterThanEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr(">=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }

                } else if (i == ExpressionTypeEnum.TYPE_LESS_THAN_EQUAL_TO.getValue()) {
                    if (!parentDateTypeConstant.lessThanEqualTo()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    tagProducer1.setCompareStr("<=");
                    tagProducer1.setValue(conditionsInfo.getData1());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                } else if (i == ExpressionTypeEnum.TYPE_LIKE.getValue()) {
                    if (!parentDateTypeConstant.like()) {
                        break;
                    }
                    TagProducer tagProducer1 = new TagProducer();
                    tagProducer1.setColumnName(columnName);
                    tagProducer1.setCompareStr(" LIKE ");
                    tagProducer1.setValue("%" + conditionsInfo.getData1() + "%");
                    tagProducer1.setConditionsClassEnumValue(conditionsClassEnum.getValue());
                    if (joinPrivateFlag) {
                        tagPrivateProducers.add(tagProducer1);
                    } else {
                        tagProducers.add(tagProducer1);
                    }
                }
            }
        }
       return forPublicTagProducer(tagProducers, tagPrivateProducers, tagId);
    }

    public Integer forPublicTagProducer(List<TagProducer> tags, List<TagProducer> tagPrivateProducers, Long tagId) {
        //根据条件字段所在表分组
        Map<Integer, List<TagProducer>> tagsMap = tags.stream().collect(Collectors.groupingBy(TagProducer::getConditionsClassEnumValue));
        if (CollectionUtils.isEmpty(tagsMap) && CollectionUtils.isEmpty(tagPrivateProducers)) {
            return 0;
        }
        DecimalFormat decimalFormat = new DecimalFormat("0000");
        String format = GradeTypeEnum.TYPE_PUBLIC.getValue() + decimalFormat.format(tagId);
        List<Integer> keys = new ArrayList<>(tagsMap.keySet());
        StringBuilder builder = new StringBuilder("SELECT CONCAT(" + format + ",t0.mct_no)," + tagId + ",t0.mct_no,'" + DateTimeUtil.formatYYYY_MM_DD_HH_MM_SS(LocalDateTime.now()) + "' FROM ");
        //连表sql构造
        for (int i = 0; i < keys.size(); i++) {
            Integer conditionsClassEnumValue = keys.get(i);
            //表名
            String tableName = ConditionsClassEnum.getTableNameByValue(conditionsClassEnumValue);
            if (i == 0) {
                if (conditionsClassEnumValue.equals(ConditionsClassEnum.BANK_PUBLIC_PRIMITIVE.getValue())) {
                    builder.append("( SELECT mct_no,mct_id_number FROM " + tableName);
                } else if (conditionsClassEnumValue.equals(ConditionsClassEnum.BANK_PUBLIC_INDUSTRY.getValue())) {
                    builder.append("( WITH RECURSIVE split_mct_industry AS ( SELECT mct_no, SUBSTRING_INDEX(mct_industry, ',', 1) AS mct_industry, SUBSTRING( mct_industry, LENGTH( SUBSTRING_INDEX(mct_industry, ',', 1) ) + 2 )AS rest FROM tbl_bank_public_primitive UNION ALL SELECT mct_no, SUBSTRING_INDEX(rest, ',', 1) AS mct_industry, SUBSTRING( rest, LENGTH( SUBSTRING_INDEX(rest, ',', 1) ) + 2 )AS rest FROM split_mct_industry WHERE rest != '' ) SELECT mct_no, mct_industry FROM split_mct_industry");
                } else {
                    builder.append("( SELECT mct_no FROM " + tableName);
                }
            } else {
                if (conditionsClassEnumValue.equals(ConditionsClassEnum.BANK_PUBLIC_PRIMITIVE.getValue())) {
                    builder.append(" INNER JOIN ( SELECT mct_no,mct_id_number FROM " + tableName);
                } else if (conditionsClassEnumValue.equals(ConditionsClassEnum.BANK_PUBLIC_INDUSTRY.getValue())) {
                    builder.append(" INNER JOIN ( WITH RECURSIVE split_mct_industry AS ( SELECT mct_no, SUBSTRING_INDEX(mct_industry, ',', 1) AS mct_industry, SUBSTRING( mct_industry, LENGTH( SUBSTRING_INDEX(mct_industry, ',', 1) ) + 2 )AS rest FROM tbl_bank_public_primitive UNION ALL SELECT mct_no, SUBSTRING_INDEX(rest, ',', 1) AS mct_industry, SUBSTRING( rest, LENGTH( SUBSTRING_INDEX(rest, ',', 1) ) + 2 )AS rest FROM split_mct_industry WHERE rest != '' ) SELECT mct_no, mct_industry FROM split_mct_industry");
                } else {
                    builder.append(" INNER JOIN ( SELECT mct_no FROM " + tableName);
                }
            }
            builder.append(" WHERE ");
            List<TagProducer> tbProducers = tagsMap.get(conditionsClassEnumValue);
            for (int j = 0; j < tbProducers.size(); j++) {
                TagProducer tbProducer = tbProducers.get(j);
                if (j != 0) {
                    builder.append(" AND ");
                }
                if (tbProducer.getOrFlag() == 1) {
                    builder.append("(");
                    for (int k = 0; k < tbProducer.getOrList().size(); k++) {
                        TagProducerForOR forOR = tbProducer.getOrList().get(k);
                        if ((k != 0)) {
                            builder.append(" OR ");
                        }
                        builder.append(tbProducer.getColumnName() + forOR.getCompareStr() + "'" + forOR.getValue() + "'");
                    }
                    builder.append(")");
                    continue;
                }
                if (tbProducer.getCompareStr().contains("IN")) {
                    builder.append(tbProducer.getColumnName() + tbProducer.getCompareStr() + tbProducer.getValue());
                } else {
                    builder.append(tbProducer.getColumnName() + tbProducer.getCompareStr() + "'" + tbProducer.getValue() + "'");
                }

            }
            builder.append(") t" + i);
            if (i > 0) {
                builder.append(" ON t" + i + ".mct_no = t" + (i - 1) + ".mct_no");
            }
        }
        if (tagPrivateProducers.size() > 0) {
            String sql = manageTagInfoService.forPublicJoinPrivateTagProducer(tagPrivateProducers);
            if (!keys.contains(ConditionsClassEnum.BANK_PUBLIC_PRIMITIVE.getValue())) {
                if (CollectionUtils.isEmpty(tagsMap)) {
                    builder.append(ConditionsClassEnum.BANK_PUBLIC_PRIMITIVE.getTableName()).append(" t0");
                } else {
                    builder.append(" INNER JOIN ").append(ConditionsClassEnum.BANK_PUBLIC_PRIMITIVE.getTableName()).append(" tm").append(" ON t0.mct_no = tm.mct_no");
                }
            }
            builder.append(" INNER JOIN ").append(sql).append(" tp").append(" ON mct_id_number = vip_id_card_no");
        }
        builder.append(" GROUP BY t0.mct_no");
        String sql = builder.toString();
        sql="select count(1) from ("+sql+") a";
        log.info("forPublicTagProducer 拼接sql: " + sql);
        int tagVipNum = tagHisDao.getEstimate(sql);
        return tagVipNum;
    }

}
